﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CvModel;

namespace DAL
{

    public class SearchDB
    {
        private static CvModelContainer _context = new CvModelContainer();

        public static List<Applicant> SearchForEverything(string searchString)
        {
            var everythingQuery =
                (from a in _context.Applicant
                    from c in _context.Cv
                    from m in _context.Merits
                    from ex in _context.Experiences
                    from e in _context.Educations
                    where
                        (m.Desription.Contains(searchString) || ex.Description.Contains(searchString) ||
                        e.Desription.Contains(searchString) ||
                        c.Interests.Contains(searchString) || c.Miscellaneous.Contains(searchString) ||
                        c.PersonalQualities.Contains(searchString)) &&
                        (m.CvCvId == c.CvId && ex.CvCvId == c.CvId &&
                        e.CvCvId == c.CvId)  
                       && c.ApplicantApplicantId == a.ApplicantId
                    select a).Distinct().ToList();
            return everythingQuery;
        }

        public static List<Applicant> SearchAllMeritsRanked(string searchString)
        {
            var meritQuery =
    (from m in _context.Merits
     from c in _context.Cv
     from a in _context.Applicant
     where m.Desription.Contains(searchString) && m.CvCvId == c.CvId && c.ApplicantApplicantId == a.ApplicantId
     orderby m.Ranking descending
     select a).Distinct().ToList();

            return meritQuery;
        }

        public static List<Applicant> SearchAllEducations(string searchString)
        {
            var educationQuery =
                (from e in _context.Educations
                 from c in _context.Cv
                 from a in _context.Applicant
                 where e.Desription.Contains(searchString) && e.CvCvId == c.CvId && c.ApplicantApplicantId == a.ApplicantId
                 select a).Distinct().ToList();

            return educationQuery;
        }

        public static List<Applicant> SearchAllExperiencesRanked(string searchString)
        {
            var experienceQuery =
    (from ex in _context.Experiences
     from c in _context.Cv
     from a in _context.Applicant
     where ex.Description.Contains(searchString) && ex.CvCvId == c.CvId && c.ApplicantApplicantId == a.ApplicantId
     orderby ex.Ranking descending
     select a).Distinct().ToList();

            return experienceQuery;
        }
    }
}
